package com.excel.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {
	
	/**不为空**/
	public static final int NOTEMPTY = 0;
	/**数字**/
	public static final int NUMBER = 1;
	
	private Workbook wb;
	/**
	 * 字段对应关系
	 */
	private Map<Integer, String> fields;
	/**
	 * 验证规则
	 */
	private Map<Integer, List<Integer>> validates;
	/**
	 * 验证结果
	 */
//	private Map<Integer, List<String>> validateResult;
	
	private List<String> validateResult;
	
	/**
	 * 总列数
	 */
	private int totalCells;
	
	private Sheet sheet;
	
	private int rownum;
	
	
	/**
	 * 导出
	 */
	public ExcelUtils() {
		this.fields = new HashMap<Integer, String>();
		rownum = 0;
		wb = new HSSFWorkbook();
		sheet = wb.createSheet("sheet");
	}
	
	/**
	 * 导入
	 * @param startRow
	 * @throws IOException 
	 */
	public ExcelUtils(InputStream is,String filename,int totalCells) throws IOException {
		this.fields = new HashMap<Integer, String>();
		this.validates = new HashMap<Integer, List<Integer>>();
		this.totalCells = totalCells;
		if(isExcel2003(filename)) {
			wb = new HSSFWorkbook(is);
		} else if(isExcel2007(filename)) {
			wb = new XSSFWorkbook(is);
		} else {
			throw new RuntimeException("不是Excel文件");
		}
	}

	/**
	 * 解析Excel
	 * @author huff
	 * @param is
	 * @throws IOException
	 * Date:2017年7月24日下午5:54:12
	 */
	public <T> List<T> readExcel( Class<T> beanClass) throws IOException {
		return this.readExcel(beanClass, null);
	}
	
	public <T> List<T> readExcel(Class<T> beanClass,ExcelAction<T> action) throws IOException {
		// 得到第一个shell
		sheet = wb.getSheetAt(0);
		// 得到Excel的行数
		int totalRows = sheet.getPhysicalNumberOfRows();
		// 得到Excel的列数(前提是有行数)
//		if (totalRows >= 1 && sheet.getRow(0) != null) {
//			totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
//		}
		List<T> dataList = new ArrayList<T>(totalRows);
		for (int r = 0; r < totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null) continue;
			Map<String, Object> data = setData(row,r);
			if(action != null) {
				if(!action.operate(r,data)) {
					continue;
				}
			}
			if(data.get("validate") == null) data.put("validate", true);
			if(!(Boolean)data.get("validate")) {
				validateResult.add("第" + (r + 1) + "行数据错误");
				continue;
			}
			T t = null;
			if(beanClass.equals(Map.class)) {
				t = (T) data;
				dataList.add(t);
			} else {
				t = JsonUtils.mapToObj(data,beanClass);
				dataList.add(t);
			}
			if(action != null) {
				action.operate(r,t);
			}
			
		}
		return dataList;
	}
	
	
	/**
	 * 输出验证结果
	 * @author huff
	 * @param result
	 * @return
	 * Date:2017年7月25日下午3:30:04
	 */
//	public ExcelUtils validateResult(Map<Integer, List<String>> result) {
//		this.validateResult = result;
//		return this;
//	}
	public ExcelUtils validateResult(List<String> result) {
		this.validateResult = result;
		return this;
	}
	
	/**
	 * 增加字段关联
	 * @author huff
	 * @param row 行
	 * @param field 字段名
	 * @return
	 * Date:2017年7月25日下午1:42:14
	 */
	public ExcelUtils addField(int row,String field,Integer ... validate) {
		if(StringUtils.isNotEmpty(field)) {
			this.fields.put(row, field);
		}
		if(validate != null && validate.length > 0) {
			this.validates.put(row, Arrays.asList(validate));
		}
		return this;
	}
	
	/**
	 * 判断是否是2003的excel，返回true是2003
	 * @author huff
	 * @param filename
	 * @return
	 * Date:2017年7月24日下午5:54:28
	 */
	public boolean isExcel2003(String filename) {
		return filename.matches("^.+\\.(?i)(xls)$");
	}

	/**
	 * 判断是否是2007的excel，返回true是2007
	 * @author huff
	 * @param filename
	 * @return
	 * Date:2017年7月24日下午5:54:56
	 */
	public boolean isExcel2007(String filename) {
		return filename.matches("^.+\\.(?i)(xlsx)$");
	}
	
	/**
	 * 生成数据
	 * @author huff
	 * @param row
	 * @return
	 * Date:2017年7月25日下午1:54:41
	 */
	private Map<String, Object> setData(Row row,int rownum) {
		Map<String, Object> data = new HashMap<String, Object>();
		for (int c = 0; c < totalCells; c++) {
			Cell cell = row.getCell(c);
			String key = StringUtils.isNotBlank(fields.get(c)) ? fields.get(c) : String.valueOf(c);
			Object value = null;
			if(cell == null) {
				value = null;
			} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				value = cell.getNumericCellValue();
			} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
				value = cell.getStringCellValue();
			} else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
				value = cell.getNumericCellValue();
			}
//			if(value == null && cell != null) {
//				System.err.println(cell.getCellType());
//			}
			if(!validateData(value, c, rownum)) {
				data.put("validate", false);
			}
			
			data.put(key, value);
		}
		return data;
	}
	
	//验证数据
	private boolean validateData(Object data,int column,int rownum) {
		
		if(validateResult == null) return true;
		if(!validates.containsKey(column)) return true;
		boolean flag = true;
		List<Integer> validateList = validates.get(column);
		for (Integer validate : validateList) {
			if(validate == NOTEMPTY) {
				if(data == null || StringUtils.isBlank(String.valueOf(data))) {
//					addValidateResult(column, "第"+(rownum+1)+"行数据错误");
					flag = false;
				}
			} else if(validate == NUMBER) {
				if(data == null || StringUtils.isBlank(String.valueOf(data)) || !NumberUtils.isNumber(String.valueOf(data))) {
//					addValidateResult(column, "第"+(rownum+1)+"行数据错误");
					flag = false;
				}
			}
		}
		return flag;
	}
	
	//增加验证结果
//	private void addValidateResult(int column,String errorMsg) {
//		if(!validateResult.containsKey(column)) {
//			validateResult.put(column, new ArrayList<String>());
//		}
//		validateResult.get(column).add(errorMsg);
//	}
	
	/********************下载模板*********************/
	public ExcelUtils addData(List<String> titles,List<Map<String, Object>> datas) {
		return addData(titles, datas, null);
	}
	/**
	 * 
	 * @author huff
	 * @param titles
	 * @param datas
	 * @param style key 行,列 value 样式
	 * @return
	 * Date:2017年8月23日上午11:44:13
	 */
	public ExcelUtils addData(List<String> titles,List<Map<String, Object>> datas,Map<String, Object> style) {
		//增加标题行
		Row headeRow = sheet.createRow(rownum++);
		for (int i = 0; i < titles.size(); i++) {
			Cell cell = headeRow.createCell(i);
			cell.setCellValue(titles.get(i));
			if(style != null && style.containsKey( rownum + "," + i)) {
				cell.setCellStyle(getCellStyle());
			}
		}
		//增加数据
		if(datas != null) {
			for (Map<String, Object> data : datas) {
				Row row = sheet.createRow(rownum++);
				for (int i = 0; i < titles.size(); i++) {
					String key = fields.containsKey(i) ? fields.get(i) : String.valueOf(i);
					Cell cell = addCell(row, i, data.get(key));
					if(style != null && style.containsKey( rownum + "," + i)) {
						cell.setCellStyle(getCellStyle());
					}
				}
			}
		}
		return this;
	}
	/**
	 * 自动调整宽度
	 * @author huff
	 * @param columns
	 * @return
	 * Date:2017年8月23日下午3:47:46
	 */
	public ExcelUtils autoSizeColumn(List<Integer> columns) {
		if(CollectionUtils.isNotEmpty(columns)) {
			for (Integer s : columns) {
				sheet.autoSizeColumn(s);
			}
		}
		return this;
	}
	
	/**
	 * 添加单元格
	 * @author huff
	 * @date 2016年1月29日
	 * @param row
	 * @param colunm
	 * @param val
	 * @return
	 */
	private Cell addCell(Row row, int colunm, Object val) {
		Cell cell = row.createCell(colunm);
		try {
			if (val == null) {
				
			} else if (val instanceof String){
				cell.setCellValue((String)val);
			} else if (val instanceof Integer) {
				cell.setCellValue((Integer) val);
			} else if (val instanceof Double) {
				cell.setCellValue((Double) val);
			} else if (val instanceof Character) {
				cell.setCellValue((String)val);
			} else {
				cell.setCellValue(String.valueOf(val));
			}
		} catch (Exception e) {
			cell.setCellValue(String.valueOf(val));
		}
		return cell;
	}
	
	private CellStyle getCellStyle() {
		CellStyle style = wb.createCellStyle();
		Font font = wb.createFont();
		font.setColor(HSSFColor.RED.index);
		style.setFont(font);
		return style;
	}
	
	/**
	 * 输出数据
	 * @author huff
	 * @param response
	 * @param fileName
	 * @throws IOException
	 * Date:2017年7月27日下午3:35:03
	 */
	public void write(HttpServletResponse response, String fileName) throws IOException{
		response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+new String(fileName.getBytes("gb2312"),"iso8859-1"));
		wb.write(response.getOutputStream());
	}

}
